/*DONE* Table [dbo].[<CompanyName>$LSC Hospitality Type$5ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 14.1.2021 - JI ******/
/*Add prestaging tables for all companies in LSInsight$Companies table*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyCounter as INT
DECLARE @i as INT

SET @i = 0
SET @CompanyCounter = (SELECT
		MAX([Company])
	FROM LSInsight$Companies)

WHILE @i <= @CompanyCounter 
BEGIN
SET @Company = (SELECT
		LSInsight$Companies.[CompanyPrefix]
	FROM dbo.LSInsight$Companies
	WHERE [Company] = @i)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].[' + @Company + '$LSC Hospitality Type$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC (@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].[' + @Company + '$LSC Hospitality Type$5ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[Restaurant No_] [nvarchar](10) NOT NULL,
	[Sequence] [int] NOT NULL,
	[Sales Type] [nvarchar](20) NOT NULL,
	[Description] [nvarchar](50) NOT NULL,
	[Layout View] [int] NOT NULL,
	[Dining Area ID] [nvarchar](20) NOT NULL,
	[KDS Display_Printing] [int] NOT NULL,
	[Auto Logoff at POS Exit] [tinyint] NOT NULL,
	[Access To Other Restaurant] [nvarchar](10) NOT NULL,
	[Layout Function Menu ID] [nvarchar](20) NOT NULL,
	[Service Flow ID] [nvarchar](20) NOT NULL,
	[Tips Panel ID] [nvarchar](20) NOT NULL,
	[Active KDS Load Config_] [nvarchar](20) NOT NULL,
	[After Transaction Posted] [int] NOT NULL,
	[Tips Income Acc_ 1] [nvarchar](20) NOT NULL,
	[Tips Income Acc_ 2] [nvarchar](20) NOT NULL,
	[Order Guest Marking] [int] NOT NULL,
	[Guest Handling] [int] NOT NULL,
	[Order ID] [int] NOT NULL,
	[Service Type] [int] NOT NULL,
	[Guest Registration] [int] NOT NULL,
	[Staff Takeover in Trans_] [int] NOT NULL,
	[Manager Takeover in Trans_] [int] NOT NULL,
	[Open Sales POS after Logon] [int] NOT NULL,
	[Sales POS Menu Profile ID] [nvarchar](10) NOT NULL,
	[Sales POS Interface Profile ID] [nvarchar](10) NOT NULL,
	[Dir_ Sales POS Menu Profile ID] [nvarchar](10) NOT NULL,
	[Dir_ Sales POS Interf_ Prof_ID] [nvarchar](10) NOT NULL,
	[Show Voided Lines in Conf_ Win] [tinyint] NOT NULL,
	[Always Send Void_ Lines to KDS] [tinyint] NOT NULL,
	[Confirm Send to KDS] [int] NOT NULL,
	[Confirm Window Panel ID] [nvarchar](20) NOT NULL,
	[On POS Exit_Posting] [int] NOT NULL,
	[Grid Data Table ID] [nvarchar](20) NOT NULL,
	[Grid Direct Edit Menu ID] [nvarchar](20) NOT NULL,
	[Grid Order Details Menu ID] [nvarchar](20) NOT NULL,
	[Delivery Grid Panel ID] [nvarchar](20) NOT NULL,
	[Queue Counter Code] [nvarchar](20) NOT NULL,
	[Menu Type Usage] [int] NOT NULL,
	[Menu Type Deal Usage] [int] NOT NULL,
	[View Multiple Restaurants] [tinyint] NOT NULL,
	[Sharing Sales Type Filter] [nvarchar](250) NOT NULL,
	[Guest Buttons Menu Columns] [int] NOT NULL,
	[Guest Buttons] [int] NOT NULL,
	[Guest Buttons Menu Rows] [int] NOT NULL,
	[Max_ Guests Per Order] [int] NOT NULL,
	[Split Bill Panel ID] [nvarchar](20) NOT NULL,
	[Select Guest on Splitting] [tinyint] NOT NULL,
	[Combine Split Lines Action] [int] NOT NULL,
	[Guest Buttons Ordered By] [int] NOT NULL,
	[Guest Buttons on Pad] [int] NOT NULL,
	[On Seat Pop-up Selection] [int] NOT NULL,
	[Table Transfer Panel ID] [nvarchar](20) NOT NULL,
	[Send Training Trans_ to KDS] [tinyint] NOT NULL,
	[Adding Items after Bill Print] [int] NOT NULL,
	[Void Item Sent to Kitchen] [int] NOT NULL,
	[Void Item Sent to Kitch_ (Mgr)] [int] NOT NULL,
	[Void Trans_ Sent to Kitchen] [int] NOT NULL,
	[Void Trans_ Sent to Kit_ Mgr] [int] NOT NULL,
	[Sales POS Opening Mode] [int] NOT NULL,
	[Original Layout View] [int] NOT NULL,
	[Mobile Status Action Set ID] [nvarchar](10) NOT NULL,
	[Mobile KDS Confirm_ Display] [int] NOT NULL,
	[Splitting a Bill Printed Trans] [int] NOT NULL,
	[To Partial Transfer (Mgr_)] [int] NOT NULL,
	[From Partial Transfer (Mgr_)] [int] NOT NULL,
	[Move Whole Transaction (Mgr_)] [int] NOT NULL,
	[From Partial Transf_(Non-Mgr_)] [int] NOT NULL,
	[Move Whole Trans_ (Non-Mgr_)] [int] NOT NULL,
	[To Partial Transfer (Non-Mgr_)] [int] NOT NULL,
	[On Modify Order] [int] NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[$systemCreatedAt] [datetime] NOT NULL,
	[$systemCreatedBy] [uniqueidentifier] NOT NULL,
	[$systemModifiedAt] [datetime] NOT NULL,
	[$systemModifiedBy] [uniqueidentifier] NOT NULL,
 CONSTRAINT [' + @Company + '$LSC Hospitality Type$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[Restaurant No_] ASC,
	[Sequence] ASC,
	[Sales Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [' + @Company + '$LSC Hospitality Type$5ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END
SET @i = @i + 1
END
